/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.ExamDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author Arturia
 */
public class ExamDAO {

    public static String generateID(Connection con) {
        String id = null;
        try {
            String sql = "SELECT MAX(numericID) "
                    + "FROM (SELECT 'numericID' = "
                    + "CASE WHEN ISNUMERIC(exam_id) = '1' THEN CAST(exam_id_id AS INT) "
                    + "ELSE NULL END FROM t_exam) ALIAS";
            PreparedStatement pre = con.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                int iID = Integer.parseInt(id = rs.getString(1));
                id = String.valueOf(iID + 1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public static ArrayList LoadDatabase(Connection con, ExamDTO exam) {
        ArrayList<ExamDTO> result = new ArrayList<ExamDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {
            if (exam == null) {
                sql = "SELECT exam.exam_id as 'exam_id', examtype.type_name as 'type_name',"
                        + "exam.date_from as 'date_from', exam.date_to as 'date_to',"
                        + "exam.requirement as 'requirement', exam.exam_status as 'status',"
                        + "exam.total_percent as 'total_percent', subjectexam.score as 'score'"
                        + "FROM t_exam exam LEFT JOIN t_exam_type examtype ON (exam.type_id = examtype.type_id)"
                        + ", t_subject_exam_student subjectexam"
                        + "WHERE exam.exam_id = subjectexam.exam_id";
                stm = con.prepareStatement(sql);
            }

            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                ExamDTO examTemp = new ExamDTO();
                examTemp = new ExamDTO();

                examTemp.setStrExamID(rs.getString(1));

                if (rs.wasNull()) {
                    examTemp.setStrExamID("");
                }

                examTemp.setStrExamType(rs.getString(2));

                if (rs.wasNull()) {
                    examTemp.setStrExamType("");
                }

                examTemp.setDtDateFrom(new Date(rs.getTimestamp(3).getTime()));

                if (rs.wasNull()) {
                    examTemp.setDtDateFrom(new Date());
                }

                examTemp.setDtDateTo(new Date(rs.getTimestamp(4).getTime()));

                if (rs.wasNull()) {
                    examTemp.setDtDateTo(new Date());
                }

                examTemp.setStrRequirement(rs.getString(5));

                if (rs.wasNull()) {
                    examTemp.setStrRequirement("");
                }

                examTemp.setStrStatus(rs.getString(6));

                if (rs.wasNull()) {
                    examTemp.setStrStatus("");
                }

                examTemp.setFlTotal(rs.getFloat(7));

                if (rs.wasNull()) {
                    examTemp.setFlTotal(Float.NaN);
                }

                examTemp.setFlScore(rs.getFloat(8));

                if (rs.wasNull()) {
                    examTemp.setFlScore(Float.NaN);
                }


                result.add(examTemp);
            }
            stm.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public static int insert(Connection con, ExamDTO exam) {
        int result = 0;
        try {
            String sql = "INSERT INTO t_exam VALUES(?,?,?,?,?,?,?)";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, exam.getStrExamID());
            stm.setString(2, exam.getStrExamType());
            stm.setDate(3, (java.sql.Date) exam.getDtDateFrom());
            stm.setDate(4, (java.sql.Date) exam.getDtDateTo());
            stm.setString(5, exam.getStrRequirement());
            stm.setString(6, exam.getStrStatus());
            stm.setFloat(7, exam.getFlTotal());
            result = stm.executeUpdate();

            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int update(Connection con, ExamDTO exam) {
        int result = 0;
        try {
            String query = "UPDATE t_exam SET exam_type=?, date_from=?, date_to=?,"
                    + "requirement=?, status=?, total_percent=? "
                    + "WHERE exam_id=?";
            PreparedStatement stm = con.prepareStatement(query);
            stm.setString(1, exam.getStrExamType());
            stm.setDate(2, (java.sql.Date) exam.getDtDateFrom());
            stm.setDate(3, (java.sql.Date) exam.getDtDateTo());
            stm.setString(4, exam.getStrRequirement());
            stm.setString(5, exam.getStrStatus());
            stm.setFloat(6, exam.getFlTotal());
            stm.setString(7, exam.getStrExamID());

            result = stm.executeUpdate();
            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int delete(Connection con, String examID) {
        int result = 0;
        try {
            String sql = "DELETE FROM t_exam WHERE exam_id=?";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, examID);
            result = stm.executeUpdate();
            stm.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static ArrayList<ExamDTO> getAllExamsOfAnSubject(Connection con, String subjectid) {
        ArrayList<ExamDTO> result = new ArrayList<ExamDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {
            sql = "SELECT exam.exam_id as 'exam_id', examtype.type_name as 'type_name', "
                    + "exam.date_from as 'date_from', exam.date_to as 'date_to', "
                    + "exam.requirement as 'requirement', exam.exam_status as 'status', "
                    + "exam.total_percent as 'total_percent', subjectexam.score as 'score' "
                    + "FROM t_exam exam LEFT JOIN t_exam_type examtype ON (exam.type_id = examtype.type_id) "
                    + ", t_subject_exam_student subjectexam "
                    + "WHERE exam.exam_id = subjectexam.exam_id "
                    + "AND subjectexam.subject_id = ?";
            stm = con.prepareStatement(sql);
            stm.setString(1, subjectid);
            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                ExamDTO examTemp = new ExamDTO();
                examTemp = new ExamDTO();

                examTemp.setStrExamID(rs.getString(1));

                if (rs.wasNull()) {
                    examTemp.setStrExamID("");
                }

                examTemp.setStrExamType(rs.getString(2));

                if (rs.wasNull()) {
                    examTemp.setStrExamType("");
                }

                examTemp.setDtDateFrom(new Date(rs.getTimestamp(3).getTime()));

                if (rs.wasNull()) {
                    examTemp.setDtDateFrom(new Date());
                }

                examTemp.setDtDateTo(new Date(rs.getTimestamp(4).getTime()));

                if (rs.wasNull()) {
                    examTemp.setDtDateTo(new Date());
                }

                examTemp.setStrRequirement(rs.getString(5));

                if (rs.wasNull()) {
                    examTemp.setStrRequirement("");
                }

                examTemp.setStrStatus(rs.getString(6));

                if (rs.wasNull()) {
                    examTemp.setStrStatus("");
                }

                examTemp.setFlTotal(rs.getFloat(7));

                if (rs.wasNull()) {
                    examTemp.setFlTotal(Float.NaN);
                }

                examTemp.setFlScore(rs.getFloat(8));

                if (rs.wasNull()) {
                    examTemp.setFlScore(Float.NaN);
                }
                result.add(examTemp);
            }
            stm.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}
